﻿using nplJobsCommon.Common;
using nplJobsCommon.JobSeeker;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace nplJobsDAL.Select
{
    public class SelectJobByJobAlert : DataAccessBase
    {
        public int LocationID { get; set; }
        public int JobCategoryID { get; set; }
        public int JobTypeID { get; set; }
        public int SalaryID { get; set; }
        public DataTable Skill { get; set; }

        public SelectJobByJobAlert()
        {
            StoredProcedureName = StoredProcedure.Name.spSelectJobsByJobAlert.ToString();
        }

        #region GetJobs
        public List<JobToDisplay> GetJobs()
        {
            DataSet dsJob;
            List<JobToDisplay> lst = new List<JobToDisplay>();
            try
            {
                SelectJobParameters selectProfessionalInfoParameters = new SelectJobParameters(this.JobCategoryID, this.JobTypeID, this.SalaryID, this.LocationID, this.Skill);

                using (SqlConnection con = new SqlConnection(base.ConnectionString))
                {
                    con.Open();                       

                    SqlCommand cmd = new SqlCommand(StoredProcedureName);
                    cmd.Parameters.Add("@SalaryID", SqlDbType.Int).Value = SalaryID;
                    cmd.Parameters.Add("@JobCategoryID", SqlDbType.Int).Value = JobCategoryID;
                    cmd.Parameters.Add("@JobTypeID", SqlDbType.Int).Value = JobTypeID;
                    cmd.Parameters.Add("@LocationID", SqlDbType.Int).Value = LocationID;
                    cmd.Parameters.Add("@Skills", SqlDbType.Structured).Value = Skill;
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.ExecuteNonQuery();
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    dsJob = new DataSet();
                    adp.Fill(dsJob);

                    con.Close();
                }
                lst = GetList(dsJob);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }
        #endregion

        #region SQL parameters for selecting 
        public class SelectJobParameters
        {
            public int JobCategoryID { get; set; }
            public int JobTypeID { get; set; }
            public int SalaryID { get; set; }
            public int LocationID { get; set; }
            public DataTable Skill { get; set; }
            private SqlParameter[] _parameters;

            public SelectJobParameters(int jobCategoryID, int jobTypeID, int salaryID, int locationID, DataTable skill)
            {
                this.JobCategoryID = jobCategoryID;
                this.JobTypeID = jobTypeID;
                this.SalaryID = salaryID;
                this.LocationID = locationID;
                this.Skill = skill;

                Build();
            }
            private void Build()
            {
                SqlParameter[] parameters = 
                {
                    new SqlParameter("@JobCategoryID",  this.JobCategoryID),
                    new SqlParameter("@JobTypeID", this.JobTypeID),
                    new SqlParameter("@SalaryID", this.SalaryID),
                    new SqlParameter("@LocationID", this.LocationID),
                    new SqlParameter("@Skill",  this.Skill),
                };

                Parameters = parameters;
            }

            public SqlParameter[] Parameters
            {
                get { return _parameters; }
                set { _parameters = value; }
            }
        }
        #endregion

        #region GetList
        private List<JobToDisplay> GetList(DataSet dsAppliedJobToDisplay)
        {
            List<JobToDisplay> jobToDisplay;
            try
            {
                jobToDisplay = (from n in dsAppliedJobToDisplay.Tables[0].AsEnumerable()
                                select new JobToDisplay
                                   {
                                       JobID = n.Field<Int64>("JobID"),
                                       CompanyImage = n.Field<string>("CompanyImage"),
                                       ApplicationDateline = n.Field<int>("ApplicationDateline"),
                                       JobTitle = n.Field<string>("JobTitle"),
                                       CompanyName = n.Field<string>("CompanyName"),
                                       PostedDate = n.Field<DateTime>("PostedDate")
                                   }).ToList();

                return jobToDisplay;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            finally { }
        }
        #endregion

    }
}
